# -*- coding:utf-8 -*-
"""
user表实现增删改查
"""
import logging
import sqlite3


from LibrarySystem.config.config import Config
from LibrarySystem.repositories.sqlite3.interface.user_data_interface import UserDataInterface
from LibrarySystem.repositories.sqlite3.models.user_models import User

module_logger = logging.getLogger("logger")


class UserRepository(UserDataInterface):
    """
    @ClassName：UserRepository
    @Description： 继承用户数据层接口类，本项目只创建一个实例
    负责实现接口功能
    @Author：锦沐Python
    """
    def __init__(self):
        self.config = Config()
        self.connection = None
        self.cursor = None
        # 建立数据库连接
        self.connection = sqlite3.connect(self.config.SQLite_DB_PATH, check_same_thread=False, timeout=20)
        self.connection.row_factory = self._dict_factory

    def _dict_factory(self, cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def _execute_mysql(self, sql_command, data=None):
        try:
            # self.connection.ping(reconnect=True)
            self.cursor = self.connection.cursor()

            if data:
                self.cursor.execute(sql_command, data)
            else:
                return (False, f"操作数据为空")

            if sql_command.lower().strip().startswith("select"):
                # 如果是查询语句，获取查询结果
                result = self.cursor.fetchall()
                return (True, result)
            else:
                # 对于增删改操作，提交事务
                self.connection.commit()
                affected_rows = self.cursor.rowcount
                return (True, f"操作成功，影响行数: {affected_rows}")

        except Exception as e:
            # 发生异常时回滚事务
            self.connection.rollback()
            module_logger.error(f"数据库操作失败: {e}")
            return (False, f"数据库操作失败: {e}")


    def add_user(self, user: User):
        """
         增加用户到数据库
        :param user:
        :return: (操作结果标志, 数据|提示信息)
        """
        user_id = user.user_id
        try:
            sql = "SELECT 1 FROM users WHERE user_id = ? LIMIT 1"
            data = (user_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag and result:
                module_logger.info("users 已存在")
                return (False, f"用户 {user_id} 已存在")

            # 写入数据
            sql = "INSERT INTO users (user_id, user_name, email, role, password, account_status, registration_time)" \
                  " VALUES (?, ?, ?, ?, ?, ?, ?)"
            data = (user.user_id, user.user_name, user.email,
                    user.role , user.password , user.account_status,
                    user.registration_time,)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag:
                module_logger.info(f"用户 {user_id} 添加成功")
                return (True, f"用户 {user_id} 添加成功")
            else:
                module_logger.error(f"添加用户失败 {user_id}")
                return (False, f"添加用户失败 {user_id}")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"添加用户失败：{e}")
            return (False, f"添加用户失败 {user_id}： {e}")


    def update_user(self, user: User):
        """
        根据user_id更新用户信息
        :param user:
        :return: (操作结果标志, 数据|提示信息)
        """
        user_id = user.user_id

        try:
            # 读数据
            sql = "SELECT * FROM users WHERE user_id = ? LIMIT 1"
            data = (user_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"用户{user_id}不存在，更新失败")
                return (False, f"用户{user_id}不存在，更新失败")

            # 写入数据
            sql = "UPDATE users SET user_name = ?, email = ?," \
                         " role = ?, password = ?," \
                         " registration_time = ? WHERE user_id = ?"
            data = ( user.user_name, user.email,
                    user.role, user.password,
                    user.registration_time, user.user_id)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag:
                module_logger.info(f"用户 {user_id} 更新成功")
                return (True, f"用户 {user_id} 更新成功")
            else:
                module_logger.error(f"更新失败 {user_id}")
                return (False, f"更新失败 {user_id}")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"修改用户信息失败：{e}")
            return (False, f"修改用户信息失败：{e}")

    def query_user(self, user_id: str):
        """
        查询用户
        :param user_id:
        :return: (操作结果标志, 数据|提示信息)
        """
        try:
            # 读数据
            sql = "SELECT * FROM users WHERE user_id = ? LIMIT 1"
            data = (user_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            print(result)
            if flag is False:
                module_logger.info(f"用户{user_id}不存在")
                return (False, f"用户{user_id}不存在")

            # 创建一个 User 对象 result
            if result:
                get_user = User(**result[0])
                return (True, get_user)
            else:
                module_logger.info(f"用户{user_id}不存在")
                return (False, f"用户{user_id}不存在")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"查询用户信息失败：{e}")
            return (False, f"查询用户信息失败：{e}")

    def del_user(self, user_id: str):
        """
        删除用户信息
        :param user_id:
        :return: (操作结果标志, 数据|提示信息)
        """
        try:
            # 读数据
            sql = "SELECT 1 FROM users WHERE user_id = ? LIMIT 1"
            data = (user_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"用户{user_id}不存在，无法删除")
                return (False, f"用户{user_id} 不存在，无法删除")

            # 写入数据
            sql = "DELETE FROM users WHERE user_id = ?"
            data = (user_id)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag:
                return (True, f"用户{user_id} 已删除")
            else:
                return (False, f"删除用户{user_id} 失败")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"删除用户信息失败：{e}")
            return (False, f"删除用户信息失败：{e}")
